#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 此脚本用来提取密码字典中符合条件的密码-实时查询数据库里弱密码
import datetime
import json
import time

import bcrypt  # 版本4.1.1，高版本会报错
import pymysql

# 公司的密码导出文件包含id,md5密码,salt
file_path = 'C:/Users/yangjianzhang/Desktop/'
# file_path = './'
file_name = '测试'

host = 'rm-2zej74580n3kmf93w9o.mysql.rds.aliyuncs.com'
user = 'cloudpc_prod'
password = 'k_z8_tKhS7WF_RCm'
database = 'cloudpc_prod'
charset = 'utf8'


# 验证密码
def password_verify(passwd, hashed):
    passwd = passwd.encode('utf-8')
    hashed = hashed.encode('utf-8')
    return bcrypt.checkpw(passwd, hashed)


# select distinct u.id,u.mobile,u.`password` from pc_user u where u.`password` is not null
def handle_pw(pwd, user_pwd, user_id):
    pw_flag = '0'
    try:
        print('用户' + user_id + '正在检测密码' + pwd)
        flag = password_verify(pwd, user_pwd)
        if flag:
            pw_flag = '1'
    except Exception as e:
        print(user_id + "\t" + user_pwd + '\t' + '异常:' + str(e))
        pw_flag = 'E'
    if pw_flag == '1':
        update_sql = "update pc_user_password_verify set password = '{}',flag = '{}' where user_id = '{}'".format(
            pwd, pw_flag, user_id)
        execute(update_sql)
    return pw_flag


def get_connection():
    # print("线程ID:", thread_id)
    con = pymysql.connect(host=host, user=user, password=password, db=database,
                          charset=charset)
    return con


def execute(sql):
    con = get_connection()
    print(sql)
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


def select_by_sql(sql):
    con = get_connection()
    cursor = con.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    rows = []
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = str(value)
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    con.close()
    return js


if __name__ == '__main__':

    # 该文件存储所有的简单密码，例如123这种密码
    password_data = open(file_path + "简单密码库.txt", encoding='UTF-8')
    count = 0
    ok_count = 0
    passlist = []
    # 将所有简单密码放入内存
    for count, line in enumerate(password_data):
        pwd = line.strip("\n")
        # print(type(pwd))
        passlist.append(pwd)
    password_data.close()

    # 查询数据的用户
    flag = True
    i = 1
    while flag:
        i = i + 1
        if i > 1000000:
            break
        sql = "SELECT * FROM pc_user u where  not exists (select user_id from pc_user_password_verify p where p.user_id = u.id) and u.`password` is not null and u.`password` <> '' limit 1"
        rs = select_by_sql(sql)
        rs = json.loads(rs)
        if len(rs) > 0:
            user_pwd = rs[0]['password']
            user_id = rs[0]['id']
            begin = int(time.time())
            inset_sql = "insert into pc_user_password_verify (user_id,flag,password,create_time) values ('{}','{}','{}',now())".format(
                user_id, '0', '')
            execute(inset_sql)
            for pwd in passlist:
                handle_pw(pwd, user_pwd, user_id)
            end = int(time.time())
            print('id:%s 耗时%d秒' % (user_id, (end - begin)))
        else:
            flag = False

    print('结束')
